from matplotlib import pyplot as plt
import pandas as pd # type: ignore
from sqlalchemy import create_engine # type: ignore
import seaborn as sns
# PostgreSQL 配置
postgres_host = "172.16.137.39"  
postgres_port = "5432"  
postgres_user = "a2513220218"  
postgres_password = "xawl-6043"  
postgres_database = "a2513220218" 
postgres_schema = "datawarehouse" 
# 建立连接字符串 
conn_string = f"postgresql://{postgres_user}:{postgres_password}@{postgres_host}:{postgres_port}/{postgres_database}"
# 使用 SQLAlchemy 创建数据库连接
engine = create_engine(conn_string) 
conn = engine.connect() 
# 从数据库查询数据 
query_product_quantity = """
SELECT 
    dc.cust_firstname,
    SUM(fs.sls_price) AS total
FROM 
    {}.fact_sales fs
JOIN 
    {}.dim_customer dc ON fs.cust_num = dc.cust_num
GROUP BY 
    dc.cust_firstname
ORDER BY 
    total 
    limit 10;
""".format(postgres_schema,postgres_schema)

customer_data = pd.read_sql(query_product_quantity, conn)
# 可视化
plt.figure(figsize=(10, 6))
sns.barplot(x='total', y='cust_firstname', data=customer_data, palette='viridis')
plt.title('Total customer_data Sold by Customer')
plt.xlabel('Total customer_data')
plt.ylabel('Customer Name')
plt.show()

# 关闭数据库连接
conn.close()
engine.dispose()